package com.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author Khaled Lela
 */
public class SearchSql {

    private Connection con;
    private PreparedStatement ps;
    private ResultSet rs;
    private Object[][] data;

    public SearchSql() {
        try {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
            con = DriverManager.getConnection("jdbc:derby:BreadDB;");
        } catch (Exception ex) {
        }
    }

    //Search for  name which like Person Name case(1)
    public Object[][] getPeopleSearch(String personName) {
        ArrayList list1 = new ArrayList();
        ArrayList list2 = new ArrayList();
        ArrayList list3 = new ArrayList();
        ArrayList list4 = new ArrayList();
        ArrayList list5 = new ArrayList();
        ArrayList list6 = new ArrayList();
        try {
            ps = con.prepareStatement("SELECT P.PersonID , P.PersonName , P.Person_Num , P.Category, "
                    + " D.Name , D.Region "
                    + " FROM People P , Distributor D "
                    + " WHERE P.DistributorID = D.DistributorID "
                    + " AND "
                    + " P.PersonName LIKE ? ");
            ps.setString(1, "%" + personName + "%");
            rs = ps.executeQuery();
            while (rs.next()) {
                list1.add(rs.getInt("PersonID") + "");
                list2.add(rs.getString("PersonName"));
                list3.add(rs.getInt("Person_Num") + "");
                list4.add(rs.getString("Name"));
                list5.add(rs.getString("Region"));
                list6.add(rs.getString("Category"));
            }
            data = new Object[list1.size()][6];
            int i = 0;
            for (int j = 0; j < list1.size(); j++) {
                data[j][5] = list1.get(i);
                data[j][4] = list2.get(i);
                data[j][3] = list3.get(i);
                data[j][2] = list4.get(i);
                data[j][1] = list5.get(i);
                data[j][0] = list6.get(i);
                i++;
            }
        } catch (Exception e) {
        } finally {
            shutdownCon();
        }
        return data;
    }

    //Search with Identical Id number Case(2)
    public Object[][] getPeopleSearch(int id) {
        ArrayList list1 = new ArrayList();
        ArrayList list2 = new ArrayList();
        ArrayList list3 = new ArrayList();
        ArrayList list4 = new ArrayList();
        ArrayList list5 = new ArrayList();
        ArrayList list6 = new ArrayList();
        try {
            ps = con.prepareStatement("SELECT P.PersonID , P.PersonName , P.Person_Num , P.Category, "
                    + " D.Name , D.Region "
                    + " FROM People P , Distributor D "
                    + " WHERE P.DistributorID = D.DistributorID "
                    + " AND "
                    + " P.PersonID = ? ");
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                list1.add(rs.getInt("PersonID") + "");
                list2.add(rs.getString("PersonName"));
                list3.add(rs.getInt("Person_Num") + "");
                list4.add(rs.getString("Name"));
                list5.add(rs.getString("Region"));
                list6.add(rs.getString("Category"));
            }
            data = new Object[list1.size()][6];
            int i = 0;
            for (int j = 0; j < list1.size(); j++) {
                data[j][5] = list1.get(i);
                data[j][4] = list2.get(i);
                data[j][3] = list3.get(i);
                data[j][2] = list4.get(i);
                data[j][1] = list5.get(i);
                data[j][0] = list6.get(i);
                i++;
            }
        } catch (Exception e) {
        } finally {
            shutdownCon();
        }
        return data;
    }

    //shutdown database connection
    private void shutdownCon() {
        try {
            DriverManager.getConnection("jdbc:derby:;shutdown=true");
        } catch (Exception ex) {
        }
    }
}
